package net.zjitc.dao;

import net.zjitc.domain.User;
import net.zjitc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/18.
 */
public class Userdao {
    public int pageSize=3;
    public List<User> fingPage(int page) throws SQLException {
        //链接C3P0数据池
        Connection conn= C3P0Utils.getConnection();
        String sql="select * from User order by id limit ?,?";
        PreparedStatement ps= conn.prepareStatement(sql);
        ps.setInt(1,(page-1)*2);
        ps.setInt(2,pageSize);
        ResultSet result = ps.executeQuery();
        List list = new ArrayList<>();
        while(result.next()){
            User user = new User();
            user.setId(result.getInt(1));
            user.setUsername(result.getString(2));
            user.setPassword(result.getString(3));
            list.add(user);
        }
        return list;
    }


    public List<User> fingAll() throws SQLException {
        Connection conn= C3P0Utils.getConnection();
        String sql="select * from User";
        PreparedStatement ps= conn.prepareStatement(sql);
        ResultSet result = ps.executeQuery();
        //使用集合，存放查出来的对象
        List list = new ArrayList<>();
        while(result.next()){
            User user = new User();
            user.setId(result.getInt(1));
            user.setUsername(result.getString(2));
            user.setPassword(result.getString(3));
            list.add(user);
        }
        return list;
    }

    public List<User> findAllByUtils() throws SQLException {
        //执行SQL对象
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from User";
        //使用封装策略自动封装
        //结果集的封装策略：ResultSetHandle类
        //因为结果集有不同的种类，可能是一个集合，也可能是一个对象
        //所以使用ResultSetHandle类的子类，封装集合的时ResultSetHandle，封闭一个对象的ResultHandle类
        //User.class时封装的擦靠模型，  javaBean
        List list=(List) runner.query(sql,new BeanListHandler(User.class));
        return list;
    }
    public User findById(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from User where id = ?";
        User user = (User) runner.query(sql,new BeanHandler(User.class),id);
        return user;
    }
}
